package top.canyue.eca_manager.mapper;

import org.apache.ibatis.annotations.*;
import top.canyue.eca_manager.bean.Conference;
import top.canyue.eca_manager.provider.ConferenceProvider;

import java.util.List;
import java.util.Map;

@Mapper
public interface ConferenceMapper {
    @Select(value = "SELECT * FROM conference WHERE id = #{id} AND isDelete= 0")
    Conference findConferenceById(String id);

    @Select(value = "SELECT id,name FROM conference WHERE NOT isDelete")
    List<Map<String, String>> getAllConferenceName();

    @Select(value = "SELECT * FROM conference WHERE isDelete = 0 AND name LIKE CONCAT('%',#{name},'%') ORDER BY #{orders} DESC LIMIT #{offset},#{fetchSize} ")
    List<Conference> findConferenceByName(String name, String orders, Integer offset, Integer fetchSize);

    @Select(value = "SELECT * FROM conference WHERE isDelete = 0 ORDER BY #{orders} DESC LIMIT #{offset},#{fetchSize} ")
    List<Conference> findAllByName(String orders, Integer offset, Integer fetchSize);

    @Select(value = "SELECT COUNT(id) FROM conference WHERE isDelete = 0 AND name LIKE CONCAT('%',#{name},'%')")
    Integer getCountByName(String name);

    @Insert(value = "INSERT INTO conference(id, name, address, startTime, endTime, comm, isDelete, createTime) VALUES(" +
            "#{id},#{name},#{address},#{startTime},#{endTime},#{comm},0,NOW()" +
            ")")
    Integer addConference(Conference conference);

    @UpdateProvider(type = ConferenceProvider.class, method = "updateConferenceInfo")
    Integer updateConferenceInfo(Conference conference);

    @Update(value = "UPDATE conference SET isDelete = 1,updateTime = Now() WHERE id = #{id}")
    Integer deleteById(String id);


    @Select(value="SELECT COUNT(id) FROM conference WHERE isDelete = 0 AND startTime > now()")
    Integer getCountByFuture();

    @Select(value="SELECT COUNT(id) FROM conference WHERE isDelete = 0 AND DATE_FORMAT(startTime,'%Y%m%d') = DATE_FORMAT(CURDATE(),'%Y%m%d')")
    Integer getCountByToday();

    @Select(value = "SELECT persistent FROM conference WHERE id = #{id} AND isDelete = 0 LIMIT 1")
    Integer getPersistentById(String id);

    //设法尽可能上避开全表扫描 conference达到eq_ref detail达到ref
    @Select(value = "SELECT cd.conference_id FROM" +
            "(SELECT conference_id FROM conferencedetail WHERE user_id = #{userId} AND isDelete = 0) AS cd " +
            "JOIN (SELECT id,endTime FROM conference WHERE isDelete = 0 AND persistent = 0 AND endTime > NOW()) AS c " +
            "ON cd.conference_id = c.id " +
            "ORDER BY c.endTime " +
            "LIMIT 1")
    String getFirstConferenceByUserId(String userId);

    @SelectProvider(type = ConferenceProvider.class, method = "getUserConferenceByDay")
    List<Conference> getUserConferenceByDay(String userId, String date);

    @SelectProvider(type = ConferenceProvider.class, method = "getUsersHaveConferenceDaysByMonth")
    List<Integer> getUsersHaveConferenceDaysByMonth(String userId, int year, int month);

}
